package org.gitee.simpleutil.util;

import cn.hutool.core.io.IoUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.gitee.simpleutil.core.IOUtil;
import org.jumpmind.symmetric.csv.CsvReader;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;

/**
 * Csv 转 Xlsx 工具类
 */
@Slf4j
public class CsvToXlsxUtil {

    /**
     * CSV常用分隔符，如需动态扩展设置成配置项
     */
    private static final char[] DELIMITERS = {
            ',',
            ';',
            '\001',
            ' ',
            '\t',
            '|',
            '#',
            '&'
    };

    private static void writeToExcel(HttpServletResponse response, byte[] data, String fileName) {
        ServletOutputStream output = null;
        try {
            // 设置输出格式
            response.setContentType("application/octet-stream");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            output = response.getOutputStream();
            output.write(data);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            IOUtil.closeQuietly(output);
        }
    }

    /**
     * @param inputStream 输入流
     */
    public static void csv2xlsx(InputStream inputStream, String fileName, HttpServletResponse response) {
        csvStream2xlsxStream(inputStream, fileName, response);
    }

    /**
     * 读取CSV文件并写入到XLSX文件中，指定CSV文件编码
     *
     * @param csvFilePath 文件地址
     * @param charset     编码
     */
    public static String csvToXlsx(String csvFilePath, String charset) {
        String xlsxFileAddress = "";
        FileOutputStream fileOutputStream = null;
        try {
            char delimiter = getDelimiter(csvFilePath);
            xlsxFileAddress = csvFilePath.replace("csv", "xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet(getSheetName(csvFilePath));
            int rowNum = -1;
            CsvReader csvReader = new CsvReader(csvFilePath, delimiter, Charset.forName(charset));
            while (csvReader.readRecord()) {
                rowNum++;
                XSSFRow currentRow = sheet.createRow(rowNum);
                for (int i = 0; i < csvReader.getColumnCount(); i++) {
                    String value = csvReader.get(i);
                    currentRow.createCell(i).setCellValue(value);
                }
            }
            fileOutputStream = new FileOutputStream(xlsxFileAddress);
            workBook.write(fileOutputStream);
            return xlsxFileAddress;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            IoUtil.close(fileOutputStream);
        }
        return xlsxFileAddress;
    }

    public static void main(String[] args) {
        String csvFilePath = "D:\\项目说明\\9、批量操作\\精度丢失测试";
        String toXlsxPath = csvToXlsx(csvFilePath, "UTF-8");
        System.out.println(toXlsxPath);
    }

    /**
     * @param inputStream 输入流
     */
    private static void csvStream2xlsxStream(InputStream inputStream, String fileName, HttpServletResponse response) {
        FileOutputStream fileOutputStream = null;
        try {
            fileName = fileName.replace(".csv", ".xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet("sheet1");
            int rowNum = -1;
            CsvReader csvReader = new CsvReader(inputStream, Charset.forName("UTF-8"));
            while (csvReader.readRecord()) {
                rowNum++;
                XSSFRow currentRow = sheet.createRow(rowNum);
                for (int i = 0; i < csvReader.getColumnCount(); i++) {
                    currentRow.createCell(i).setCellValue(csvReader.get(i) + "\t");
                }
            }
            File file = new File("/" + fileName);
            fileOutputStream = new FileOutputStream(file);
            workBook.write(fileOutputStream);
            //            InputStream fis = new FileInputStream(file);
            //            byte[] bytes = fis.readAllBytes();
            writeToExcel(response, null, fileName);
            file.delete();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            IoUtil.close(fileOutputStream);
        }
    }

    /**
     * 设置excel文件的sheet名称
     * 获取CSV文件名作为Excel文件的sheet名称
     *
     * @param path 资源路径
     */
    private static String getSheetName(String path) {
        try {
            String[] file = getFileName(path).split("\\.");
            return file[0];
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return "Sheet";
        }
    }

    /**
     * 根据资源路径切割获取文件名
     *
     * @param path 资源路径
     */
    private static String getFileName(String path) {
        String[] paths = path.contains("\\") ? path.split("\\\\") : path.split("/");
        return paths[paths.length - 1];
    }

    /**
     * 常用CSV分隔符数组遍历资源第一行，分隔的字段数多的为资源分隔符
     * 异常情况下默认用’,‘作为分隔符
     *
     * @param path 资源路径
     */
    private static char getDelimiter(String path) {
        BufferedReader br = null;
        char delimiter = ',';
        try {
            br = new BufferedReader(new FileReader(path));
            String line = br.readLine();
            CsvReader csvReader;
            int columCount = 0;
            for (char delimiterTest : DELIMITERS) {
                csvReader = new CsvReader(getStringStream(line), delimiterTest, StandardCharsets.UTF_8);
                if (csvReader.readRecord()) {
                    int newColumnCount = csvReader.getColumnCount();
                    if (newColumnCount > columCount) {
                        columCount = newColumnCount;
                        delimiter = delimiterTest;
                    }
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            IoUtil.close(br);
        }
        return delimiter;
    }

    /**
     * 字符串转输入流
     * 把CSV文件第一行数据转成输入流
     *
     * @param sInputString 字符串
     */
    private static InputStream getStringStream(String sInputString) {
        if (null != sInputString && !"".equals(sInputString)) {
            try {
                return new ByteArrayInputStream(sInputString.getBytes());
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }


    private static String getBOM() {
        byte b[] = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};
        return new String(b);
    }

}